from flask import render_template, redirect, flash, session
from flask_paginate import Pagination, get_page_parameter
import datetime

from app_db import *
from src.forms import *
from src.model.base_opera import *
from src.login_token import generate_confirmation_token, confirm_token
from src.permission import admin_required
from src.db_init import get_admin, get_default_role, init
from src.sqoop import SqoopCmd
from src.model.models import SqoopInfo, User


# init db
@app.before_first_request
def db_init():
    db.create_all()
    table_info = Role.query.filter_by(is_valid=1).first()
    if not table_info:
        init()


# 登陆
@app.route('/')
def index():
    return redirect(url_for('login'))


@app.route('/index')
def l_index():
    return redirect(url_for('login'))


@app.route('/login', methods=['GET', 'POST'])
def login():
    if session.get('name'):
        flash(message='已登录')
        return redirect(url_for('web_dim_product'))
    form = LoginForm()
    if form.validate_on_submit():
        user = User.query.filter_by(email=form.email.data).first()
        if user and user.verify_password(form.password.data):
            login_user(user, form.remember_me.data)
            next = request.args.get('next')
            if next:
                next = next.replace('/', '')

            if not next:
                next = 'web_dim_product'
            session['id'] = user.id
            session['name'] = user.name

            flash(message='登陆成功!')
            return redirect(url_for(next))
        else:
            flash(message='邮箱或密码错误')
            return redirect(url_for('login'))

    return render_template('login.html', form=form)


# 登出
@app.route('/logout')
@login_required
def logout():
    logout_user()
    session.clear()
    flash(message='您已退出')
    return redirect(url_for('login'))


# 注册
@app.route('/register', methods=['GET', 'POST'])
def register():
    user = User.query.filter_by(is_valid=1).first()
    if user:
        role_id = get_default_role()
        default_role = True
    else:
        role_id = get_admin()
        default_role = False

    print('role_id: {}'.format(role_id))
    if session.get('name'):
        flash(message='已登录')
        return redirect(url_for('web_dim_product'))
    form = RegisterForm()

    if form.validate_on_submit():
        email = User.query.filter_by(email=form.email.data).first()
        user = User.query.filter_by(name=form.name.data).first()
        if email or user:
            flash(message="用户或邮箱已存在")
            return redirect(url_for('register'))

        user_password = User()
        user_password.password(password=form.password.data)

        user = User(
            email = form.email.data,
            name = form.name.data,
            password_hash = user_password.password_hash,
            registered_on = datetime.datetime.now(),
            role_id = int(role_id),
            is_valid = True
        )
        db.session.add(user)
        db.session.commit()
        return redirect(url_for('login'))
    return render_template('register.html', form=form, default_role=default_role)


# 用户管理
@app.route('/user_manager', methods=['GET', 'POST'])
@login_required
@admin_required
def user_manager():
    form = UserForm()
    if form.validate_on_submit():
        email = User.query.filter_by(email=form.email.data).first()
        user = User.query.filter_by(name=form.name.data).first()
        if email or user:
            flash(message="用户或邮箱已存在")

        user_password = User()
        user_password.password(password='123456')

        user = User(
            email = form.email.data,
            name = form.name.data,
            password_hash = user_password.password_hash,
            registered_on = datetime.datetime.now(),
            role_id = int(form.role_id.data.id),
            is_valid = True
        )
        db.session.add(user)
        db.session.commit()
        return redirect(url_for('user_manager'))

    users = []
    if read_user():
        for data in read_user():
            if data.get('id') != current_user.id:
                users.append(data)

    return render_template('user_manager.html', user_info = users, form=form)


# 用户删除与密码重置
@app.route('/user_manager/modify')
def user_manager_modify():
    user_info = User.query.filter_by(email=request.args.get('email')).first()
    is_valid = int(request.args.get('is_valid')) if request.args.get('is_valid') else -1
    passwd = int(request.args.get('passwd')) if request.args.get('passwd') else None
    if passwd:
        passwd = generate_password_hash(config.REST_PASSWD)
    else:
        passwd = user_info.password_hash

    if is_valid != -1:
        is_valid = True if is_valid == 1 else False
    else:
        is_valid = user_info.is_valid

    user_info.id = user_info.id
    user_info.name = request.args.get('name') if request.args.get('name') else user_info.name
    user_info.password_hash = passwd
    user_info.registered_on = user_info.registered_on
    user_info.email = request.args.get('email')
    user_info.role_id = request.args.get('role_id') if request.args.get('role_id') else user_info.role_id
    user_info.is_valid = is_valid

    db.session.commit()
    if request.args.get('passwd'):
        flash(message='密码重置为: {}'.format(config.REST_PASSWD))
    if request.args.get('is_valid'):
        flash(message='用户 {} 删除完成'.format(user_info.name))
    return redirect(url_for('user_manager'))


# 用户修改信息
@app.route('/user/modify', methods=['GET', 'POST'])
def user_modify():
    form = PersonModifyForm()
    user_id = current_user.id
    if user_id:
        query_info = User.query.join(Role, Role.id == User.role_id).\
            add_columns(User.email, User.name.label('username'), User.registered_on, Role.name.label('rolename')).\
            filter(User.id == user_id).first()

        user_info = {
            'email': query_info.email,
            'name': query_info.username,
            'registered_on': query_info.registered_on,
            'role': '管理员' if query_info.rolename == config.ADMIN_ROLE else '普通用户'
        }
    else:
        user_info = None

    if request.method == 'POST':
        form_data = request.form.to_dict()
        email = form_data.get('email')
        passwd = form_data.get('password')

        if email:
            jug_email = User.query.filter_by(email=email).first()
            if jug_email:
                flash(message='邮箱已存在')
                return redirect(url_for('user_modify'))
            else:
                json_data = {
                    'id': current_user.id,
                    'email': email,
                }
                if passwd:
                    user_password = User()
                    user_password.password(password=passwd)
                    passwd = user_password.password_hash
                    json_data.update({
                        'password_hash': passwd
                    })
                    opera_user.post_and_put(request_json=json_data)
                    flash(message='操作成功')
                    session.clear()
                    return redirect(url_for('login'))
                opera_user.post_and_put(request_json=json_data)
                flash(message='修改邮箱成功')
                return redirect(url_for('user_modify'))

        if passwd:
            user_password = User()
            user_password.password(password=passwd)
            passwd = user_password.password_hash
            json_data = {
                'id': current_user.id,
                'password_hash': passwd
            }
            opera_user.post_and_put(request_json=json_data)
            flash(message='修改密码成功')
            session.clear()
            return redirect(url_for('login'))

    return render_template('user_modify.html', user_info=user_info, form=form)


# 产品管理
@app.route('/web_dim_product', methods=['GET', 'POST'])
@login_required
def web_dim_product():
    form = Product()
    if form.validate_on_submit():
        form_data = DimProduct.query.filter_by(product_name=form.product_name.data, is_valid=1).first()
        if form_data:
            flash('数据已存在')
        else:
            post_data = {
                'product_name': form.product_name.data,
                'product_describe': form.product_describe.data,
                'owner_id': current_user.id
            }
            form.product_name.data = None
            form.product_describe.data = None

            opera_dim_product.post(post_json=post_data)
            flash('提交成功')
        return redirect(url_for('web_dim_product'))

    r = opera_dim_product.get()
    json_data = r.get_json().get('data') if r else []

    return render_template('product.html', form=form, json_data=json_data)


# 数据库驱动类型查询
@app.route('/web_dim_db_driver_type', methods=['GET', 'POST'])
@login_required
def web_dim_db_driver_type():
    form = DbDriverType()
    if form.validate_on_submit():
        form_data = read_dim_db_driver_type(db_driver_name=form.db_driver_name.data)
        if form_data:
            flash('数据已存在')
        else:
            json_data = {
                'db_driver_name': form.db_driver_name.data,
                'db_driver_describe': form.db_driver_describe.data,
                'owner_id': current_user.id
            }
            form.db_driver_name.data = None
            form.db_driver_describe.data = None

            opera_dim_db_driver_type.post(requst_json=json_data)
            flash('提交成功')
        redirect(url_for('web_dim_db_driver_type'))

    req = opera_dim_db_driver_type.get()
    if req:
        json_data = req.get_json().get('data') if req.get_json() else []
    else:
        json_data = []

    return render_template('db_driver.html', json_data=json_data, form=form)


# 数据类型查询
@app.route('/web_dim_data_type', methods=['GET', 'POST'])
@login_required
def web_dim_data_type():
    form = DataType()
    if form.validate_on_submit():
        data_type_name = form.data_type_name.data
        db_driver_id = form.db_driver_id.data.id

        is_post = read_dim_data_type(data_type_name=data_type_name, db_driver_id=int(db_driver_id))
        if is_post:
            flash(message='数据已存在')
        else:
            json_data = {
                'data_type_name': data_type_name,
                'data_type_describe': form.data_type_describe.data,
                'db_driver_id': db_driver_id,
                'owner_id': current_user.id
            }
            form.data_type_name.data = None
            form.data_type_describe.data = None
            form.db_driver_id.data = None

            opera_dim_data_type.post(requst_json=json_data)
            flash(message='提交成功')
        return redirect(url_for('web_dim_data_type'))

    req = opera_dim_data_type.get()
    if req:
        json_data = req.get_json().get('data') if req.get_json() else []
    else:
        json_data = []
    return render_template('data_type.html', json_data=json_data, form=form)


# #################   数据库    ##################
# dim_db
@app.route('/web_dim_db', methods=['GET', 'POST'])
@login_required
def web_dim_db():
    product_id = request.args.get('product_id')
    if product_id and product_id != 'None':
        form = DbbDriverRelate()

        if form.validate_on_submit():
            db_name = request.form.get('db_name')
            db_describe = request.form.get('db_describe')
            db_driver_id = request.form.get('db_type')
            is_post = read_dim_db(db_name=db_name, db_driver_id=db_driver_id, product_id=product_id)

            form.db_name.data = None
            form.db_describe.data = None
            form.db_type.data = None
            if is_post:
                flash(message='数据已存在')
            else:
                post_data = {
                    'db_name': db_name,
                    'db_describe': db_describe,
                    'db_driver_id': int(db_driver_id),
                    'product_id': int(product_id),
                    'owner_id': current_user.id
                }
                opera_dim_db.post(requst_json=post_data)
                flash(message='提交成功')
            return redirect(url_for('web_dim_db', product_id=product_id))

        json_data = read_product_db(product_id=product_id)
        for data in json_data:
            data['product_id'] = product_id
        return render_template('dim_db.html', json_data=json_data, form=form)
    else:
        form = ChoiseProduct()
        if form.validate_on_submit():
            product_id = form.product_name.data.id
            return redirect(url_for('web_dim_db', product_id=product_id))
        return render_template('dim_db_choice_product.html', form=form)


@app.route('/web_dim_db_modify', methods=['POST', 'GET'])
@login_required
def web_dim_db_modify():
    form = DbModify()

    # 注意owner_id，来自于用户的登陆信息
    if request.method == 'POST':
        req_id = request.form.get('id')
        db_name = request.form.get('db_name')
        db_describe = request.form.get('db_describe')
        db_driver_code = request.form.get('db_driver_code')
        db_driver_id = request.form.get('db_driver_id')
        product_id = request.form.get('product_id')
        owner_id = current_user.id  # 由登陆信息获得
        if req_id:
            post_data = {
                'id': req_id,
                'db_name': db_name,
                'db_describe': db_describe,
                'db_driver_code': db_driver_code,
                'db_driver_id': db_driver_id,
                'product_id': product_id,
                'owner_id': owner_id,
                'is_valid': 1
            }
            opera_dim_db.post_and_put(request_json=post_data)
            flash(message='修改成功')
            return redirect(url_for('web_dim_db'))  # url_for 为function，而不是router
        return render_template('404.html', missing_data='request.form')
    else:
        # 注意owner_id，来自于用户的登陆信息
        req_id = request.args.get('id')
        db_name = request.args.get('db_name')
        db_describe = request.args.get('db_describe')
        db_driver_code = request.args.get('db_driver_code')
        db_driver_id = request.args.get('db_driver_id')
        product_id = request.args.get('product_id')
        is_valid = request.args.get('is_valid')
        owner_id = current_user.id
        if req_id and req_id != 'None':
            update_data = {
                'id': req_id,
                'db_name': db_name,
                'db_describe': db_describe,
                'db_driver_code': db_driver_code,
                'db_driver_id': db_driver_id,
                'product_id': product_id,
                'owner_id': owner_id,
                'is_valid': is_valid
            }
            if is_valid == '0':
                opera_dim_db.post_and_put(request_json=update_data)
                flash(message='删除成功')
                return redirect(url_for('web_dim_db', product_id=product_id))
            else:
                return render_template('dim_db_modify.html', data=update_data, form=form)

        return render_template('404.html', missing_data='request.args')


# #################   表    ##################
# db_table_relate
@app.route('/web_db_table_relate', methods=['GET', 'POST'])
@login_required
def web_db_table_relate():
    db_id = request.args.get('db_id')
    if db_id and db_id != 'None' and isinstance(db_id, str):
        db_id = int(db_id)
    else:
        db_id = None

    if db_id:
        db_driver = DimDb.query.join(DimDbDriverType, DimDbDriverType.id == DimDb.db_driver_id). \
            add_columns(DimDbDriverType.db_driver_name).filter(DimDb.id == db_id).first()
        db_driver_name = db_driver.db_driver_name.upper()
        if request.method == 'POST':
            form_data = request.form.to_dict()

            table_name = form_data.get('table_name')
            table_describe = form_data.get('table_describe')

            if table_name and table_describe:
                table_info = {}
                if db_driver_name == 'HIVE':
                    table_info = {
                        'table_type': form_data.get('table_type'),
                        'line_terimial': form_data.get('line_terimial'),
                        'FIELDS_terimial': form_data.get('FIELDS_terimial'),
                        'map_terimial': form_data.get('map_terimial'),
                        'itemsplit': form_data.get('itemsplit'),
                        'save_path': form_data.get('save_path'),
                        'formatdata': form_data.get('formatdata'),
                        'partition': form_data.get('partition'),
                        'cluster': form_data.get('cluster'),
                        'sorted': form_data.get('sorted'),
                        't_num': form_data.get('t_num')
                    }

                if db_driver_name == 'MYSQL':
                    table_info = {
                        'storageengine': form_data.get('storageengine'),
                        'charset': form_data.get('charset')
                    }

                if table_info:
                    is_post = read_db_table_relate(db_id=db_id, table_name=form_data.get('table_name'))
                    if is_post:
                        flash(message='数据已存在')
                    else:
                        json_data = {
                            'db_id': db_id,
                            'table_name': form_data.get('table_name'),
                            'table_describe': form_data.get('table_describe'),
                            'table_info': table_info,
                            'owner_id': current_user.id
                        }
                        opera_db_table_relate.post_and_put(request_json=json_data)

                        flash(message='提交成功')

                return redirect(url_for('web_db_table_relate', db_id=db_id))

        req = opera_db_table_relate.get(db_id=db_id)
        product_info = DimDb.query.filter_by(id=db_id).first()
        if req:
            json_data = req.get_json().get('data') if req.get_json() else []
            product_id = product_info.product_id
            return render_template('db_table.html', json_data = json_data, db_id=db_id,
                                   product_id=product_id, db_driver_name=db_driver_name)

    return render_template('404.html', missing_data='db_id')


# 查看sql 并提交
@app.route('/web_table_sql', methods=['GET', 'POST'])
@login_required
def web_table_sql():
    table_id = request.args.get('table_id')
    if table_id and table_id != 'None':
        db_info = DbTableRelate.query.join(DimDb, DimDb.id == DbTableRelate.db_id).\
            join(DimDbDriverType, DimDbDriverType.id == DimDb.db_driver_id).\
            add_columns(DbTableRelate.db_id, DimDbDriverType.db_driver_name).\
            filter(DbTableRelate.id == table_id).first()
        db_id = db_info.db_id
        db_driver_name = db_info.db_driver_name.upper()
        if request.method == 'POST':
            flag = opera_sql.get(db_id=db_id, table_id=table_id)
            if flag:
                flash(message='数据已存在')
            else:
                form_data = request.form.to_dict()
                json_data = {
                    'db_id': db_id,
                    'table_id': table_id,
                    'sql': form_data.get('textarea')
                }
                opera_sql.post(post_json=json_data)
                flash(message='提交成功')
            return redirect(url_for('web_table_sql', table_id=table_id))

        sql = CreateTableSQL(table_id=table_id)
        if db_driver_name == 'HIVE':
            create_table_sql = sql.read_hive()
        elif db_driver_name == 'MYSQL':
            create_table_sql = sql.read_mysql()
        if create_table_sql:
            return render_template('sql.html', create_table_sql=create_table_sql, db_id=db_id, table_id=table_id)
    return render_template('404.html', missing_data='table_id')


# db_table_relate modify
@app.route('/web_db_table_relate_modify', methods=['POST', 'GET'])
@login_required
def web_db_table_relate_modify():
    if request.method == 'POST':
        req_id = request.form.get('id')
        db_id = request.form.get('db_id')
        table_id = request.form.get('table_id')
        table_name = request.form.get('table_name')
        table_describe = request.form.get('table_describe')
        table_info = request.form.get('table_info')
        owner_id = current_user.id

        post_data = {
            'id': req_id,
            'db_id': db_id,
            'table_id': table_id,
            'table_name': table_name,
            'table_describe': table_describe,
            'table_info': table_info,
            'owner_id': owner_id,
            'is_valid': 1
        }
        opera_db_table_relate.post_and_put(request_json=post_data)
        return redirect(url_for('web_db_table_relate', db_id=db_id))
    else:
        req_id = request.args.get('id')
        db_id = request.args.get('db_id')
        table_id = request.args.get('table_id')
        table_name = request.args.get('table_name')
        table_describe = request.args.get('table_describe')
        table_info = request.args.get('table_info')

        owner_id = current_user.id
        is_valid = request.args.get('is_valid')

        if req_id and req_id != 'None':
            update_data = {
                'id': req_id,
                'db_id': db_id,
                'table_id': table_id,
                'table_name': table_name,
                'table_describe': table_describe,
                'table_info': table_info,
                'owner_id': owner_id,
                'is_valid': is_valid
            }
            if is_valid == '0' or is_valid == 0:
                opera_db_table_relate.post_and_put(request_json=update_data)
                return redirect(url_for('web_db_table_relate', db_id=db_id))
            else:
                return render_template('db_table_modify.html', data=update_data, db_id=db_id)
        else:
            update_data = {
                'db_id': db_id,
                'table_id': table_id,
                'table_name': table_name,
                'table_describe': table_describe,
                'table_info': table_info,
                'owner_id': owner_id
            }
            opera_db_table_relate.post_and_put(request_json=update_data)
            return redirect(url_for('web_db_table_relate', db_id=db_id))


# #################   字段   ##################
@app.route('/web_table_column_relate', methods=['GET', 'POST'])
@login_required
def web_table_column_relate():
    db_id = request.args.get('db_id')
    table_id = request.args.get('table_id')

    if db_id and db_id != 'None' and table_id and table_id != 'None':
        db_id = int(db_id)
        table_id = int(table_id)
        if request.method == 'POST':
            column_id = request.form.get('indexid')
            if read_table_column_relate(db_id=db_id, table_id=table_id, column_id=column_id):
                flash(message='列 {} 数据已存在'.format(column_id))
            else:
                json_data = {
                    'db_id': db_id,
                    'table_id': table_id,
                    'column_id': column_id
                }

                opera_table_column_relate.post(requst_json=json_data)
                flash(message='数据提交成功！')
            return redirect(url_for('web_table_column_relate', db_id=db_id, table_id=table_id))

        req = opera_table_column_relate.get(db_id=db_id, table_id=table_id)
        if req:
            json_data = req.get_json().get('data') if req.get_json() else []
            category_info = opera_column_category.get().get_json() if opera_column_category.get() else None
            category_data = category_info.get('data') if category_info else []

            return render_template('table_column.html', json_data = json_data, db_id=request.args.get('db_id'),
                                   table_id=request.args.get('table_id'),  category_data=category_data)

    return render_template('404.html', missing_data='db_id or table_id')


# db_table_relate modify
@app.route('/web_table_column_relate_modify', methods=['POST', 'GET'])
@login_required
def web_table_column_relate_modify():
    if request.method == 'POST':
        req_id = request.form.get('id')
        db_id = request.form.get('db_id')
        table_id = request.form.get('table_id')
        column_name = request.form.get('column_name')
        data_type_id = request.values.get('data_type_id')
        data_length = request.form.get('data_length')
        column_describe = request.form.get('column_describe')

        owner_id = current_user.id

        data_length = int(data_length) if data_length and data_length != 'None' and len(data_length) > 1 else None

        post_data = {
            'id': req_id,
            'db_id': db_id,
            'table_id': table_id,
            'column_name': column_name,
            'data_type_id': data_type_id,
            'data_length': data_length,
            'column_describe': column_describe,
            'owner_id': owner_id,
            'is_valid': 1
        }

        opera_table_column_relate.post_and_put(request_json=post_data)
        return redirect(url_for('web_table_column_relate', db_id=db_id, table_id=table_id))
    else:
        req_id = request.args.get('id')
        db_id = request.args.get('db_id')
        table_id = request.args.get('table_id')
        column_name = request.args.get('column_name')
        data_type_id = request.args.get('data_type_id')
        data_length = request.args.get('data_length')
        column_describe = request.args.get('column_describe')

        owner_id = current_user.id
        is_valid = request.args.get('is_valid')

        data_length = int(data_length) if data_length and data_length != 'None' and len(data_length) > 1 else None
        if req_id and req_id != 'None':
            update_data = {
                'id': req_id,
                'db_id': db_id,
                'table_id': table_id,
                'column_name': column_name,
                'data_type_id': data_type_id,
                'data_length': data_length,
                'column_describe': column_describe,
                'owner_id': owner_id,
                'is_valid': is_valid
            }
            if is_valid == '0' or is_valid == 0:
                opera_table_column_relate.post_and_put(request_json=update_data)
                return redirect(url_for('web_table_column_relate', db_id=db_id, table_id=table_id))
            else:
                return render_template('table_column_modify.html', data=update_data, id=id,
                                       db_id=db_id, table_id=table_id)

        return render_template('404.html', missing_data='request.args')


# ###################    超链接 表维护  ######################
@app.route('/web_table', methods=['POST', 'GET'])
@login_required
def web_table():
    form = DbChoice()
    if form.validate_on_submit():
        db_id = form.db_name.data.id
        return redirect(url_for('web_db_table_relate', db_id=db_id))

    return render_template('db_query_db.html', form=form)


#  标准属性分类
@app.route('/web_dim_column_category', methods=['GET', 'POST'])
@login_required
def web_dim_column_category():
    form = ColumnCategory()
    if form.validate_on_submit():
        if opera_column_category.get(category_name=form.category_name.data):
            flash(message='数据已存在')
        else:
            json_data = {
                'category_name': form.category_name.data,
                'owner_id': current_user.id
            }
            form.category_name.data = None
            opera_column_category.post(post_json=json_data)
            flash('提交成功')
        return redirect(url_for('web_dim_column_category'))

    r = opera_column_category.get()
    if r:
        json_data = r.get_json().get('data') if r else []
    else:
        json_data = []
    return render_template('column_category.html', form=form, json_data=json_data)


#  标准字段
@app.route('/web_dim_column', methods=['GET', 'POST'])
@login_required
def web_dim_column():
    db_driver_info = DimDbDriverType.query.filter_by(is_valid=1).all()
    db_driver_data = [{
        'id': data.id,
        'db_driver_name': data.db_driver_name
    } for data in db_driver_info] if db_driver_info else []

    category_info = DimColumnCategory.query.filter_by(is_valid=1).all()
    category_data = [{
        'id': data.id,
        'category_name': data.category_name
    } for data in category_info] if category_info else []

    if request.method == 'POST':
        column_name = request.form.get('column_name')
        data_type_id = request.form.get('datatype')

        if opera_dim_column.jug_column_name(column_name=column_name, data_type_id=data_type_id):
            flash('数据已存在： {} {}'.format(column_name, data_type_id))
        else:
            data_length = int(request.form.get('data_length')) if request.form.get('data_length') else None
            category_id = int(request.form.get('columncategory')) if request.form.get('columncategory') else None
            type_id = int(request.form.get('type')) if request.form.get('type') else None

            json_data = {
                'column_name': column_name,
                'data_type_id': data_type_id,
                'data_length': data_length,
                'column_describe': request.form.get('data_describe'),
                'category_id': category_id,
                'type_id': type_id,
                'is_null': True if request.form.get('is_null') == 'True' else False,
                'is_key':  True if request.form.get('is_key') == 'True' else False,
                'defalut_value':  request.form.get('defalut_value') if request.form.get('is_key') else None,
                'owner_id': current_user.id
            }
            opera_dim_column.post(post_json=json_data)
            flash('提交成功')
        return redirect(url_for('web_dim_column'))

    # 分页呈现
    page = request.args.get(get_page_parameter(), type=int, default=1)
    r = DimColumn.query.join(DimColumnCategory, DimColumnCategory.id == DimColumn.category_id).\
            join(DimDataType, DimDataType.id == DimColumn.data_type_id). \
            join(User, User.id == DimColumn.owner_id). \
            add_columns(DimColumn.id, DimColumn.column_name, DimColumn.data_type_id, DimDataType.data_type_name,
                        DimColumn.data_length, DimColumn.column_describe, DimColumn.category_id,
                        DimColumnCategory.category_name, DimColumn.type_id, DimColumn.is_null, DimColumn.is_key,
                        DimColumn.defalut_value, DimColumn.owner_id, User.name).\
            filter(DimColumn.is_valid == 1).order_by(DimColumn.id).\
        paginate(page, per_page=config.FLASKY_POSTS_PER_PAGE, error_out=False)

    return render_template('dim_column.html', db_driver_data=db_driver_data, category_data=category_data,
                           json_data=r)


#  标准字段修改
@app.route('/dim/column/PUT', methods=['GET'])
@login_required
def web_dim_column_put():
    id = request.args.get('id')
    column_name = request.args.get('column_name')
    data_type_id = request.args.get('data_type_id')
    data_length = request.args.get('data_length')
    column_describe = request.args.get('column_describe')
    category_id = request.args.get('category_id')
    type_id = request.args.get('type_id')

    is_null = request.args.get('is_null')
    is_key = request.args.get('is_key')
    defalut_value = request.args.get('defalut_value')
    owner_id = current_user.id
    is_valid = request.args.get('is_valid')

    id = int(id) if id and id != 'None' else None
    data_type_id = int(data_type_id) if data_type_id and data_type_id != 'None' else None
    data_length = int(data_length) if data_length and data_length != 'None' else None
    category_id = int(category_id) if category_id and category_id != 'None' else None
    type_id = int(type_id) if type_id and type_id != 'None' else None
    owner_id = int(owner_id) if owner_id and owner_id != 'None' else None
    is_valid = True if int(is_valid) == 1 else False

    json_data = {
        'id': id,
        'column_name': column_name,
        'data_type_id': data_type_id,
        'data_length': data_length,
        'column_describe': column_describe,
        'category_id': category_id,
        'type_id': type_id,
        'is_null': is_null,
        'is_key': is_key,
        'defalut_value': defalut_value,

        'owner_id': current_user.id,
        'is_valid': is_valid
    }

    opera_dim_column.post_and_put(request_json=json_data)
    return redirect(url_for('web_dim_column'))


#  mapping关系
@app.route('/web_mapping_post', methods=['GET', 'POST'])
@login_required
def web_mapping_post():
    table_id = request.args.get('table_id')
    db_id = request.args.get('db_id')
    db_info = DimDb.query.filter_by(is_valid=1).all()
    db_data = [{
        'id': data.id,
        'db_name': data.db_name
    } for data in db_info] if db_info else []

    if request.method == 'POST':
        db_id = request.form.get('db')
        table_id = request.form.get('table')
        if table_id:
            return redirect(url_for('web_mapping_post', db_id=int(db_id), table_id=table_id))
        else:
            return redirect(url_for('web_mapping_post'))
    json_data = opera_mapping.get(table_id=int(table_id), db_id=db_id) if table_id else None
    if not json_data:
        json_data = read_table_column_relate(table_id=int(table_id)) if table_id else []
    return render_template('mapping.html', db_data=db_data, json_data=json_data, table_id=table_id, db_id=db_id)


# mapping post
@app.route('/web_mapping_post_post', methods=['POST'])
@login_required
def web_mapping_post_post():
    db_id = request.args.get('db_id')
    table_id = request.args.get('table_id')

    form_data = request.form.to_dict()

    if form_data and db_id and table_id:
        if 'csrf_token' in form_data:
            form_data.pop('csrf_token')
        arr = []
        for column_id, mapping_info in form_data.items():
            if mapping_info:
                if ';' in mapping_info:
                    mapping_arr = mapping_info.split(';')
                elif '；' in mapping_info:
                    mapping_arr = mapping_info.split('；')
                else:
                    mapping_arr = [mapping_info]

                for mapping_data in mapping_arr:
                    target_db, target_table, target_column = mapping_data.split('.')
                    arr.append({
                        'db_id': int(db_id),
                        'table_id': int(table_id),
                        'column_id': int(column_id),
                        'target_db': target_db,
                        'target_table': target_table,
                        'target_column': target_column,
                        'owner_id': current_user.id,
                        'is_valid': True
                })
            else:
                arr.append({
                    'db_id': int(db_id),
                    'table_id': int(table_id),
                    'column_id': int(column_id),
                    'target_db': None,
                    'target_table': None,
                    'target_column': None,
                    'owner_id': current_user.id,
                    'is_valid': True
                })
        opera_mapping.post(json_data=arr)
        flash(message='提交成功')
        return redirect(url_for('web_mapping_post', table_id=table_id))
    return render_template('404.html', missing_data = 'db_id and table_id')


#  mapping关系 搜索呈现
@app.route('/web_mapping_post_search', methods=['GET', 'POST'])
@login_required
def web_mapping_post_search():
    table_id = int(request.args.get('table_id')) if request.args.get('table_id') else None
    if table_id:
        json_data, table_flag = get_mapping(table_id=table_id)
    else:
        search_type = int(request.args.get('search_type')) if request.args.get('search_type') else None
        search_text = request.args.get('search_text')

        search_res, table_flag = search_mapping(search_type=search_type, search_text=search_text)

        search_res = search_res if search_res else []
        json_data = search_res if (search_type and search_text) else []

    if request.method == 'POST':
        search_type = request.form.get('search_type')
        search_text = request.form.get('search_text')
        return redirect(url_for('web_mapping_post_search', search_type=search_type, search_text=search_text))

    return render_template('mapping_search.html', json_data=json_data, table_flag=table_flag)


#  sqoop choice
@app.route('/web_sqoop_choice', methods=['GET', 'POST'])
@login_required
def web_sqoop_choice():
    if request.method == 'POST':
        source_db = request.form.get('source_db')
        target_save = request.form.get('target_save')
        return redirect(url_for('web_sqoop', source_db=source_db, target_save=target_save))

    return render_template('sqoop_choice.html')


#  sqoop
@app.route('/web_sqoop', methods=['GET', 'POST'])
@login_required
def web_sqoop():
    source_db = int(request.args.get('source_db')) if request.args.get('source_db') else None
    target_save = int(request.args.get('target_save')) if request.args.get('target_save') else None

    # hive table
    msg = ''
    if target_save == 1:
        msg += '若查询来源为表，则存储路径可选，若来源为查询SQL，则存储路径为必选项'

    db_db = request.args.get('db_db')
    db_table = request.args.get('db_table')

    query_info = SqoopInfo.query.join(User, User.id == SqoopInfo.owner_id).\
        add_columns(SqoopInfo.id, SqoopInfo.source_db, SqoopInfo.source_table, SqoopInfo.target_info,
                    SqoopInfo.sqoop_cmd, User.name)
    if db_db and db_table:
        query_info = query_info.filter(SqoopInfo.source_db == db_db).filter(SqoopInfo.source_table == db_table).first()
        json_data = [{
            'id': query_info.id,
            'source_db': query_info.source_db,
            'source_table': query_info.source_table,
            'target_info': query_info.target_info,
            'owner': query_info.name
        }]
    else:
        query_info = query_info.all()
        json_data = [{
            'id': data.id,
            'source_db': data.source_db,
            'source_table': data.source_table,
            'target_info': data.target_info,
            'owner': data.name
        } for data in query_info]

    if request.method == 'POST':
        form_data = request.form.to_dict()

        form_sqoop_cmd = form_data.get('sqoop_cmd')
        db_ip = form_data.get('db_ip')
        db_port = int(form_data.get('db_port')) if form_data.get('db_port') else None
        db_db = form_data.get('db_db')
        db_user = form_data.get('db_user')
        db_passwd = form_data.get('db_passwd')
        fields_terminated_by = form_data.get('fields_terminated_by')
        lines_terminated_by = form_data.get('lines_terminated_by')
        db_sql = form_data.get('db_sql')
        split_by = form_data.get('split_by')
        num_mappers = form_data.get('num_mappers')
        is_overwrite = True if int(form_data.get('is_overwrite')) == 1 else False
        save_path = form_data.get('save_path')

        increase_type = int(form_data.get('increase_type')) if form_data.get('increase_type') else None
        increase_column = form_data.get('increase_column')
        increase_data_type = int(form_data.get('increase_data_type')) if form_data.get('increase_data_type') \
            else None

        db_sql = db_sql.strip()
        sql_split = db_sql.split(' ')
        if len(sql_split) > 1:
            begin = sql_split.index('from') if sql_split.index('from') else sql_split.index('FROM')
            db_table = sql_split[begin + 1]
        else:
            db_table = db_sql

        this_info = SqoopInfo.query.join(User, User.id == SqoopInfo.owner_id).\
            add_columns(SqoopInfo.source_db, SqoopInfo.source_table, SqoopInfo.target_info, SqoopInfo.sqoop_cmd,
                        User.name).\
            filter(SqoopInfo.source_db == db_db).filter(SqoopInfo.source_table == db_table).first()
        if not this_info:
            if form_sqoop_cmd:
                sqoop_cmd = form_sqoop_cmd
                hive_db = form_data.get('hive_db')
                hive_table = form_data.get('hive_table')

            else:
                sqoop_fuc = SqoopCmd(db_ip=db_ip, db_port=db_port, db_db=db_db, user=db_user, passwd=db_passwd,
                                     sql=db_sql, fields_terminated_by=fields_terminated_by,
                                     lines_terminated_by=lines_terminated_by, split_by=split_by,
                                     num_mappers=num_mappers, is_overwrite=is_overwrite,
                                     save_path=save_path, increase_type=increase_type, increase_column=increase_column,
                                     increase_data_type=increase_data_type, source_db=source_db)

                sqoop_cmd = None
                if target_save == 1:
                    hive_db = form_data.get('hive_db')
                    hive_table = form_data.get('hive_table')
                    partition = int(form_data.get('partition')) if form_data.get('partition') else None
                    save_format = form_data.get('save_format')

                    if db_port:
                        sqoop_cmd = sqoop_fuc.sqoop_hive(stored=save_format, hive_db=hive_db, hive_table=hive_table,
                                                         partition=partition)

                elif target_save == 2:
                    save_format = form_data.get('save_format')
                    if db_port:
                        sqoop_cmd = sqoop_fuc.sqoop_hdfs(stored=save_format)

                else:
                    hive_db = form_data.get('hive_db')
                    hive_table = form_data.get('hive_table')
                    partition = int(form_data.get('partition')) if form_data.get('partition') else None
                    if db_port:
                        sqoop_cmd = sqoop_fuc.sqoop_hcatalog(hive_db=hive_db, hive_table=hive_table, partition=partition)

            if sqoop_cmd:
                target_info = save_path if target_save == 2 else json.dumps({'db': hive_db, 'table': hive_table})
                task = SqoopInfo(source_db=db_db, source_table=db_table, target_info=target_info, sqoop_cmd=sqoop_cmd,
                                 owner_id=current_user.id)
                db.session.add(task)
                db.session.commit()
                flash(message='提交成功')
        else:
            flash(message='数据已存在')
        return redirect(url_for('web_sqoop', source_db=source_db, target_save=target_save,
                                    db_db=db_db, db_table=db_table))

    return render_template('sqoop.html', target_save=target_save, json_data=json_data, msg=msg)

